USE [master]
GO
/****** Object:  Database [TrialManagement]    Script Date: 09/23/2016 09:26:06 ******/
CREATE DATABASE [TrialManagement] ON  PRIMARY 
( NAME = N'TrialManagement', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TrialManagement.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TrialManagement_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TrialManagement_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [TrialManagement] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TrialManagement].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [TrialManagement] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TrialManagement] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TrialManagement] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TrialManagement] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TrialManagement] SET ARITHABORT OFF
GO
ALTER DATABASE [TrialManagement] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [TrialManagement] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [TrialManagement] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TrialManagement] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TrialManagement] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TrialManagement] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [TrialManagement] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TrialManagement] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TrialManagement] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TrialManagement] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TrialManagement] SET  DISABLE_BROKER
GO
ALTER DATABASE [TrialManagement] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TrialManagement] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TrialManagement] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [TrialManagement] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [TrialManagement] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TrialManagement] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [TrialManagement] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [TrialManagement] SET  READ_WRITE
GO
ALTER DATABASE [TrialManagement] SET RECOVERY FULL
GO
ALTER DATABASE [TrialManagement] SET  MULTI_USER
GO
ALTER DATABASE [TrialManagement] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TrialManagement] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'TrialManagement', N'ON'
GO
USE [TrialManagement]
GO
/****** Object:  Table [dbo].[Province]    Script Date: 09/23/2016 09:26:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Province](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[IsActive] [bit] NOT NULL,
 CONSTRAINT [PK_Province] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Promoter]    Script Date: 09/23/2016 09:26:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Promoter](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](50) NOT NULL,
	[ChineseName] [nvarchar](50) NULL,
	[IsActive] [bit] NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Product]    Script Date: 09/23/2016 09:26:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Product](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](200) NULL,
	[IsActive] [bit] NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Crop]    Script Date: 09/23/2016 09:26:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Crop](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](200) NULL,
	[IsActive] [bit] NOT NULL,
 CONSTRAINT [PK_Crop] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[City]    Script Date: 09/23/2016 09:26:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[City](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[ProvinceID] [bigint] NOT NULL,
	[IsActive] [bit] NOT NULL,
 CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Trial]    Script Date: 09/23/2016 09:26:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Trial](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[CityID] [bigint] NOT NULL,
	[PromoterID] [bigint] NOT NULL,
	[ProductID] [bigint] NOT NULL,
	[CropID] [bigint] NOT NULL,
	[Assigner] [nvarchar](50) NULL,
	[Status] [nvarchar](10) NOT NULL,
	[AssignDate] [datetime] NULL,
	[CreateDate] [datetime] NULL,
	[SubmitDate] [datetime] NULL,
	[CompleteDate] [datetime] NULL,
 CONSTRAINT [PK_Trial] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[RPromoterCity]    Script Date: 09/23/2016 09:26:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RPromoterCity](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[PromoterID] [bigint] NOT NULL,
	[CityID] [bigint] NOT NULL,
	[IsActive] [bit] NOT NULL,
 CONSTRAINT [PK_RPromoterCity] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Report]    Script Date: 09/23/2016 09:26:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Report](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[TrialID] [bigint] NOT NULL,
	[IsActive] [bit] NOT NULL,
	[CreateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_Report] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Image]    Script Date: 09/23/2016 09:26:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Image](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Image] [image] NOT NULL,
	[ReportID] [bigint] NOT NULL,
	[IsActive] [bit] NOT NULL,
	[UploadDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  ForeignKey [FK_City_Province]    Script Date: 09/23/2016 09:26:06 ******/
ALTER TABLE [dbo].[City]  WITH CHECK ADD  CONSTRAINT [FK_City_Province] FOREIGN KEY([ProvinceID])
REFERENCES [dbo].[Province] ([ID])
GO
ALTER TABLE [dbo].[City] CHECK CONSTRAINT [FK_City_Province]
GO
/****** Object:  ForeignKey [FK_Trial_City]    Script Date: 09/23/2016 09:26:06 ******/
ALTER TABLE [dbo].[Trial]  WITH CHECK ADD  CONSTRAINT [FK_Trial_City] FOREIGN KEY([CityID])
REFERENCES [dbo].[City] ([ID])
GO
ALTER TABLE [dbo].[Trial] CHECK CONSTRAINT [FK_Trial_City]
GO
/****** Object:  ForeignKey [FK_Trial_Crop]    Script Date: 09/23/2016 09:26:06 ******/
ALTER TABLE [dbo].[Trial]  WITH CHECK ADD  CONSTRAINT [FK_Trial_Crop] FOREIGN KEY([CropID])
REFERENCES [dbo].[Crop] ([ID])
GO
ALTER TABLE [dbo].[Trial] CHECK CONSTRAINT [FK_Trial_Crop]
GO
/****** Object:  ForeignKey [FK_Trial_Product]    Script Date: 09/23/2016 09:26:06 ******/
ALTER TABLE [dbo].[Trial]  WITH CHECK ADD  CONSTRAINT [FK_Trial_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Product] ([ID])
GO
ALTER TABLE [dbo].[Trial] CHECK CONSTRAINT [FK_Trial_Product]
GO
/****** Object:  ForeignKey [FK_Trial_Promoter]    Script Date: 09/23/2016 09:26:06 ******/
ALTER TABLE [dbo].[Trial]  WITH CHECK ADD  CONSTRAINT [FK_Trial_Promoter] FOREIGN KEY([PromoterID])
REFERENCES [dbo].[Promoter] ([ID])
GO
ALTER TABLE [dbo].[Trial] CHECK CONSTRAINT [FK_Trial_Promoter]
GO
/****** Object:  ForeignKey [FK_RPromoterCity_City]    Script Date: 09/23/2016 09:26:06 ******/
ALTER TABLE [dbo].[RPromoterCity]  WITH CHECK ADD  CONSTRAINT [FK_RPromoterCity_City] FOREIGN KEY([CityID])
REFERENCES [dbo].[City] ([ID])
GO
ALTER TABLE [dbo].[RPromoterCity] CHECK CONSTRAINT [FK_RPromoterCity_City]
GO
/****** Object:  ForeignKey [FK_RPromoterCity_Promoter]    Script Date: 09/23/2016 09:26:06 ******/
ALTER TABLE [dbo].[RPromoterCity]  WITH CHECK ADD  CONSTRAINT [FK_RPromoterCity_Promoter] FOREIGN KEY([PromoterID])
REFERENCES [dbo].[Promoter] ([ID])
GO
ALTER TABLE [dbo].[RPromoterCity] CHECK CONSTRAINT [FK_RPromoterCity_Promoter]
GO
/****** Object:  ForeignKey [FK_Report_Trial]    Script Date: 09/23/2016 09:26:06 ******/
ALTER TABLE [dbo].[Report]  WITH CHECK ADD  CONSTRAINT [FK_Report_Trial] FOREIGN KEY([TrialID])
REFERENCES [dbo].[Trial] ([ID])
GO
ALTER TABLE [dbo].[Report] CHECK CONSTRAINT [FK_Report_Trial]
GO
/****** Object:  ForeignKey [FK_Image_Report]    Script Date: 09/23/2016 09:26:06 ******/
ALTER TABLE [dbo].[Image]  WITH CHECK ADD  CONSTRAINT [FK_Image_Report] FOREIGN KEY([ReportID])
REFERENCES [dbo].[Report] ([ID])
GO
ALTER TABLE [dbo].[Image] CHECK CONSTRAINT [FK_Image_Report]
GO
